package video;


/*
* This class is used to INSERT data into a BLOB datatype column in an Oracle8i database. 
* This class has a method called insertBlobData which takes three input parameters:
* 1) The name of the file to be added to the database
* 2) The description of the file to be added to the database
* 3) The physical path to the file, to be added to the database
* For further suggestions/improvements/help please feel free to contact me at mjauhar@usa.net
*/

/*
* Import the required Java classes
*/
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
/*
* Import the required Oracle classes
*/
import oracle.jdbc.driver.OracleDriver;
import oracle.jdbc.OracleResultSet;
import oracle.sql.BLOB;

public class Video {
	
	public Video() {
	
	
	}
	
	
	private static int insertBlobData(final String fileName, final String fileDescription, final String filePath) {
		/*
		*	 Initialize the necessary parameters
		*/
		int returnValue = 0;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		InputStream is = null;
		OutputStream os = null;
		String query = null;
		try {
			 /* 
			* Register the Oracle driver 
			*/
			DriverManager.registerDriver(new OracleDriver());
			/*
			* Establish a connection to the Oracle database. I have used the Oracle Thin driver.
			* jdbc:oracle:thin@host:port:sid, "user name", "password"
			*/
			conn = DriverManager.getConnection("jdbc:oracle:thin:@visions-bwckzjd:1521:o8i", "internal", "oracle");
			/*
			* Set auto commit to false, it helps to speed up things, by default JDBC's auto commit feature is on.
			* This means that each SQL statement is commited as it is executed.
			*/
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			/*
			* First execute a query to see if a file with the same name and description already exists.
			*/
			query = "SELECT FileId FROM tblBlobDemo WHERE FileName=\'" + fileName + "\' AND FileDescription=\'" + fileDescription + "\' ORDER BY FileId DESC";
			rs = stmt.executeQuery(query);
			if(!rs.next()) {
				/*
				* Insert all the data, for the BLOB column we use the function empty_blob(), which creates a locator for the BLOB datatype.
				* A locator is an object that ponts to the actual location of the BLOB data in the database. A locator is essential to manipulate BLOB data. 
				*/
				query = "INSERT INTO tblBlobDemo VALUES(seqFileId.NextVal,  sysdate, \'" + fileName + "\', empty_blob(), \'" + fileDescription + "\')";
				stmt.execute(query);
				/*
				* Once the locator has been inserted, we retrieve the locator by executing a SELECT statement with the FOR UPDATE clause to manually lock the row.
				*/
				query =  "SELECT FileData FROM tblBlobDemo WHERE FileName=\'" + fileName + "\' AND FileDescription=\'" + fileDescription + 
							 "\' ORDER BY FileId DESC FOR UPDATE";
				rs = stmt.executeQuery(query);
				if(rs.next()) {
					/*
					* Once a locator has been retrieved we can use it to insert the binary data into the database.
					*/
					BLOB blob = ((OracleResultSet)rs).getBLOB("FileData");
					os = blob.getBinaryOutputStream();
					final File f = new File(filePath);
					is = new FileInputStream(f);
					final byte[] buffer = new byte[blob.getBufferSize()];
					int bytesRead = 0;
					while((bytesRead = is.read(buffer)) != -1) {
						os.write(buffer, 0, bytesRead);
					}
					blob = null;
				}
				else {
					returnValue = 1;
				}
			}
			else {
				returnValue = 2;
			}
		}
		catch(SQLException e) {
			returnValue = 3;
			e.printStackTrace();
		}
		catch(FileNotFoundException e) {
			returnValue = 4;
			e.printStackTrace();
		}
		catch(Exception e) {
			returnValue = 5;
			e.printStackTrace();
		}
		finally {
			/*
			* Clean up.
			*/
			try {
				if(is != null) {
					is.close();
				}
				if(os != null) {
					os.flush();
					os.close();
				}
				if(stmt != null) {
					stmt.close();
				}
				if(rs != null) {
					rs.close();
				}
				if(conn != null) {
					conn.commit();
					conn.close();
				}
				is = null;
				os = null;
				stmt = null;
				rs = null;
				conn = null;
				query = null;
			}
			catch(Exception e) {
				returnValue = 6;
				e.printStackTrace();
			}
		}
		return returnValue;
	}
}